﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Text;

using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
//using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class EssMessageSendDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        //ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();
        
        public EssMessageSendDAL()
        {

        }
        public DataTable GetCurrentApprover(Nullable<int> emp_seq, string process, int step_no)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@process", process);
                dbManager.AddParameters(2, "@step_no", step_no);
                DataTable returnData = new DataTable();

                string sql = "select approve_path_seq, approver_type, exec_pos_group_seq, pos_seq"
                    + " from sys_approve_path path"
                    + " where process = @process"
                    + " and step_no = @step_no";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                bool bFound = false;

                if (dt.Rows.Count > 0)
                {
                    DataTable dtb2 = new DataTable();

                    if (dt.Rows[0]["approver_type"].ToString() == "1")
                    {
                        int level = 1;

                        while (!bFound && level <= 5)
                        {
                            string strForm = "";

                            for (int i = 0; i < level; i++)
                            {
                                strForm = strForm + " left join hrm_mp_positions pos@1 on pos@2.pos_working_upper_seq = pos@1.pos_seq".Replace("@1", (i + 1).ToString()).Replace("@2", i.ToString());
                            }

                            strForm = strForm + " left join sys_exec_pos_group_dtls grp on pos@1.exec_pos_seq = grp.exec_pos_seq".Replace("@1", level.ToString());

                            sql = ("select emp2.emp_seq, pos@2.pos_seq, grp.exec_pos_group_seq"
                                + (", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq")

                                + " from hrm_ps_employees emp1"
                                + " inner join hrm_mp_positions pos0 on emp1.pos_seq = pos0.pos_seq"
                                + strForm
                                + " left join hrm_ps_employees emp2 on pos@2.pos_seq = emp2.pos_seq"
                                    + " and emp2.record_status <> 'D'"
                                + " where emp1.emp_seq = @emp_seq").Replace("@2", level.ToString());
                            dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                            if (dtb2.Rows.Count > 0)
                            {
                                bFound = dtb2.Rows[0]["exec_pos_group_seq"].ToString() == dt.Rows[0]["exec_pos_group_seq"].ToString();
                                returnData = dtb2;
                            }
                            else
                            {
                                returnData = null;
                            }

                            level++;
                        }
                    }
                    else
                    {
                        sql = "select emp.emp_seq"
                            + ", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq"
                            + " from hrm_ps_employees emp"
                            + " left join hrm_ps_employee_statuses es on emp.emp_status_seq = es.emp_status_seq"
                            + " where emp.pos_seq = " + dt.Rows[0]["pos_seq"].ToString()
                            + " and es.active = '1'";
                        dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                        returnData = dtb2;
                        bFound = true;
                    }
                }

                if (!bFound)
                {
                    returnData = null;
                }

                return returnData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetNextStepNo(Nullable<int> approve_path_seq)
        {
            try
            {
                string strStepNo = "";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@approve_path_seq", approve_path_seq);

                string sql = "select path2.step_no"
                    + " from sys_approve_path path"
                    + " inner join sys_approve_path path2 on path2.step_no = path.step_no + 1"
                        + " and path2.process = path.process"
                    + " where path.approve_path_seq = @approve_path_seq";
                dbManager.Open();
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    strStepNo = dt.Rows[0]["step_no"].ToString();
                }
                else
                {
                    strStepNo = "0";
                }

                return strStepNo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public EssMessageSendInfo GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            EssMessageSendInfo info = new EssMessageSendInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.approve_path_seq, send.from_send_seq, send.message_status"

                    + ", concat(cast(user1.user_first_name as char), ' ', cast(user1.user_last_name as char)) as from_user_name"
                    + ", concat(cast(user2.user_first_name as char), ' ', cast(user2.user_last_name as char)) as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN sys_sc_users user1 ON send.from_emp_seq = user1.emp_seq"
                    + " LEFT JOIN sys_sc_users user2 ON send.to_emp_seq = user2.emp_seq"
                    + " WHERE send.send_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = string.IsNullOrEmpty(drc[0]["send_date"].ToString()) ? (DateTime?)null : DateTime.Parse(drc[0]["send_date"].ToString());
                    info.ref_seq = string.IsNullOrEmpty(drc[0]["ref_seq"].ToString()) ? (int?)null : Int32.Parse(drc[0]["ref_seq"].ToString());
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.send_text = drc[0]["send_text"].ToString();
                    info.from_emp_seq = string.IsNullOrEmpty(drc[0]["from_emp_seq"].ToString()) ? (int?)null : Int32.Parse(drc[0]["from_emp_seq"].ToString());
                    info.to_emp_seq = string.IsNullOrEmpty(drc[0]["to_emp_seq"].ToString()) ? (int?)null : Int32.Parse(drc[0]["to_emp_seq"].ToString());
                    info.from_send_seq = string.IsNullOrEmpty(drc[0]["from_send_seq"].ToString()) ? (int?)null : Int32.Parse(drc[0]["from_send_seq"].ToString());
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_seq = string.IsNullOrEmpty(drc[0]["reply_seq"].ToString()) ? (int?)null : Int32.Parse(drc[0]["reply_seq"].ToString());
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();
                    info.approve_path_seq = string.IsNullOrEmpty(drc[0]["approve_path_seq"].ToString()) ? (int?)null : Int32.Parse(drc[0]["approve_path_seq"].ToString());

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = string.IsNullOrEmpty(drc[0]["created_by"].ToString()) ? (int?)null : Int32.Parse(drc[0]["created_by"].ToString());
                    info.created_when = string.IsNullOrEmpty(drc[0]["created_when"].ToString()) ? (DateTime?)null : DateTime.Parse(drc[0]["created_when"].ToString());
                    info.updated_by = string.IsNullOrEmpty(drc[0]["updated_by"].ToString()) ? (int?)null : Int32.Parse(drc[0]["updated_by"].ToString());
                    info.updated_when = string.IsNullOrEmpty(drc[0]["updated_when"].ToString()) ? (DateTime?)null : DateTime.Parse(drc[0]["updated_when"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        public void Insert(EssMessageSendInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@send_date", info.send_date);
                dbManager.AddParameters(2, "@ref_seq", info.ref_seq);
                dbManager.AddParameters(3, "@ref_type", info.ref_type);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@send_text", info.send_text);
                dbManager.AddParameters(6, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(7, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(8, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(9, "@remarks", info.remarks);
                dbManager.AddParameters(10, "@message_status", info.message_status);
                dbManager.AddParameters(11, "@approve_path_seq", info.approve_path_seq);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", info.created_by);
                dbManager.AddParameters(14, "@created_when", info.created_when);

                strSQL = "INSERT INTO message_send"
                    + "(send_date, ref_seq, ref_type, ref_no, send_text"
                    + ", from_emp_seq, to_emp_seq, from_send_seq, remarks"
                    + ", message_status, approve_path_seq"
                    + ", record_status, created_by, created_when)"
                    + " VALUES(@send_date"
                        + ", @ref_seq"
                        + ", @ref_type"
                        + ", @ref_no"
                        + ", @send_text"
                        + ", @from_emp_seq"
                        + ", @to_emp_seq"
                        + ", @from_send_seq"
                        + ", @remarks"
                        + ", @message_status"
                        + ", @approve_path_seq"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(EssMessageSendInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@send_date", info.send_date);
                dbManager.AddParameters(2, "@ref_seq", info.ref_seq);
                dbManager.AddParameters(3, "@ref_type", info.ref_type);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@send_text", info.send_text);
                dbManager.AddParameters(6, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(7, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(8, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(9, "@remarks", info.remarks);
                dbManager.AddParameters(10, "@message_status", info.message_status);
                dbManager.AddParameters(11, "@approve_path_seq", info.approve_path_seq);

                dbManager.AddParameters(12, "@updated_by", info.updated_by);
                dbManager.AddParameters(13, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_send"
                    + " set send_date = @send_date"
                    + ", ref_seq = @ref_seq"
                    + ", ref_type = @ref_type"
                    + ", ref_no = @ref_no"
                    + ", send_text = @send_text"
                    + ", from_emp_seq = @from_emp_seq"
                    + ", to_emp_seq = @to_emp_seq"
                    + ", from_send_seq = @from_send_seq"
                    + ", remarks = @remarks"
                    + ", message_status = @message_status"
                    + ", approve_path_seq = @approve_path_seq"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(EssMessageSendInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_send"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateMessageStatus(EssMessageSendInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@message_status", info.message_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_send"
                    + " SET message_status = @message_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public string GetNextSendRefNo(string RequestType)
        {
            string strResult = "";
            int nNextRunningNo;
            DataTable dtb;
            string strSQL;
            dbManager.Open();
            dbManager.CreateParameters(2);

            dbManager.AddParameters(0, "@RequestType", RequestType);
            dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

            strResult = RequestType + "-" + string.Format("{0:yyyyMMdd}", DateTime.Today) + "-";
            strSQL = "SELECT MAX(REF_NO) AS MAX_REF_NO"
                + " FROM MESSAGE_SEND"
                + " WHERE REF_NO LIKE '" + strResult + "%'";
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count > 0)
            {
                string strText = dtb.Rows[0]["MAX_REF_NO"].ToString();
                strText = strText.Length > 0 ? strText : "000";
                nNextRunningNo = int.Parse(strText.Substring(strText.Length - 3, 3)) + 1;
                // nNextRunningNo = int.Parse(string strResult string.ri.Right(Misc.NullValue(drc[0][0], "0000").ToString(), 4)) + 1;
                strResult += nNextRunningNo.ToString("000");
            }

            return strResult;
        }
    }
}
